﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.Serialization;
using Skywave.Linq;

namespace Skywave.Data
{
    [DataContract]
    public enum ChartDataGeneratorAggregateFunctions
    {
        [EnumMember]
        Count = 1,
        [EnumMember]
        Sum = 2,
        [EnumMember]
        Average = 3,
        [EnumMember]
        Max = 4,
        [EnumMember]
        Min = 5,
    }
    [DataContract]
    [KnownType(typeof(ChartDataGeneratorAxisDateTime))]
    [KnownType(typeof(ChartDataGeneratorAxisString))]
    [KnownType(typeof(ChartDataGeneratorAxisCustomFilters))]
    public class ChartDataGenerator
    {
        public event EventHandler<ChartDataGeneratorAggregate> BeforeAggregateUse;
        public event EventHandler<ChartDataGeneratorBeforeSqlRunEventArgs> BeforeSqlRun;

        private SqlCommandString _FilterSQL;
        /// <summary>
        /// FilterSQL Property
        /// </summary>
        [DataMember]
        public SqlCommandString FilterSQL
        {
            get { return _FilterSQL; }
            set { _FilterSQL = value; }
        }

        private string _Column_Primary_Name;
        /// <summary>
        /// ColumnName_Primary Property
        /// </summary>
        [DataMember]
        public string Column_Primary_Name
        {
            get { return _Column_Primary_Name; }
            set { _Column_Primary_Name = value; }
        }

        //private string _Column_Aggregate_DesignId;
        ///// <summary>
        ///// Column_Aggregate_DesignId Property
        ///// </summary>
        //[DataMember]
        //public string Column_Aggregate_DesignId
        //{
        //    get { return _Column_Aggregate_DesignId; }
        //    set { _Column_Aggregate_DesignId = value; }
        //}

        //private string _Column_Aggregate_SQL;
        ///// <summary>
        ///// ColumnName_Aggregate Property
        ///// </summary>
        //[DataMember]
        //public string Column_Aggregate_SQL
        //{
        //    get { return _Column_Aggregate_SQL; }
        //    set { _Column_Aggregate_SQL = value; }
        //}

        //private List<string> _Column_Aggregate_JoinClauses = new List<string>();
        ///// <summary>
        ///// Column_Aggregate_JoinClauses Property
        ///// </summary>
        //[DataMember]
        //public List<string> Column_Aggregate_JoinClauses
        //{
        //    get { return _Column_Aggregate_JoinClauses; }
        //    set { _Column_Aggregate_JoinClauses = value; }
        //}

        //private ChartDataGeneratorAggregateFunctions _AggregateFunction = ChartDataGeneratorAggregateFunctions.Count;
        ///// <summary>
        ///// AggregateFunction Property
        ///// </summary>
        //[DataMember]
        //public ChartDataGeneratorAggregateFunctions AggregateFunction
        //{
        //    get { return _AggregateFunction; }
        //    set { _AggregateFunction = value; }
        //}

        private List<ChartDataGeneratorAggregate> _Aggregates = new List<ChartDataGeneratorAggregate>();
        /// <summary>
        /// Aggregates Property
        /// </summary>
        [DataMember]
        public List<ChartDataGeneratorAggregate> Aggregates
        {
            get { return _Aggregates; }
            set { _Aggregates = value; }
        }

        private List<ChartDataGeneratorAxis> _AxisGeneratorsX = new List<ChartDataGeneratorAxis>();
        /// <summary>
        /// AxisGeneratorsX Property
        /// </summary>
        [DataMember]
        public List<ChartDataGeneratorAxis> AxisGeneratorsX
        {
            get { return _AxisGeneratorsX; }
            set { _AxisGeneratorsX = value; }
        }

        private List<ChartDataGeneratorAxis> _AxisGeneratorsY = new List<ChartDataGeneratorAxis>();
        /// <summary>
        /// AxisGeneratorsY Property
        /// </summary>
        [DataMember]
        public List<ChartDataGeneratorAxis> AxisGeneratorsY
        {
            get { return _AxisGeneratorsY; }
            set { _AxisGeneratorsY = value; }
        }

        /// <summary>
        /// Filter_TableName Property
        /// </summary>
        public string Filter_TableName
        {
            get { return "[g_t_Filter]"; }
        }

        /// <summary>
        /// ...
        /// </summary>
        public string Filter_PrimaryColumn_FullName
        {
            get { return string.Format("{0}.{1}", Filter_TableName, _Column_Primary_Name); }
        }

        private string _Filter_TempTableName;
        /// <summary>
        /// ...
        /// </summary>
        public string Filter_TempTableName
        {
            get { return _Filter_TempTableName; }
        }

        public ChartData GenerateData(System.Data.Common.DbCommand dbCommand)
        {
            if (FilterSQL == null || AxisGeneratorsX == null || AxisGeneratorsX.Count == 0 || dbCommand == null || Aggregates == null || Aggregates.Count == 0)
                return null;
            string sqlRandomKey = Security.RandomGenerator.CreateRandomString(7);
            _Filter_TempTableName = string.Format("#Filter_Members_{0}", sqlRandomKey);
            List<string> sqlInits = new List<string>();
            //
            DataStructure.ListHelper<string> listHelper1;
            string s1;
            string filterPrimaryColumnName = Filter_PrimaryColumn_FullName;
            ChartData r = new ChartData();
            /*
             * SELECT
             * 
            */
            Dictionary<int, ChartDataGeneratorAxisCases> casesX = new Dictionary<int, ChartDataGeneratorAxisCases>();
            for (int iLoop1 = 0; iLoop1 < AxisGeneratorsX.Count; iLoop1++)
                casesX.Add(iLoop1, AxisGeneratorsX[iLoop1].GetCases(string.Format("g_casesX_{0}_", iLoop1), this));
            Dictionary<int, ChartDataGeneratorAxisCases> casesY = new Dictionary<int, ChartDataGeneratorAxisCases>();
            if (AxisGeneratorsY != null)
                for (int iLoop1 = 0; iLoop1 < AxisGeneratorsY.Count; iLoop1++)
                    casesY.Add(iLoop1, AxisGeneratorsY[iLoop1].GetCases(string.Format("g_casesY_{0}_", iLoop1), this));
            //
            sqlInits.Add(string.Format(@"
CREATE TABLE {0}
(
	[Id] INT
	CONSTRAINT [PK_{0}] PRIMARY KEY CLUSTERED 
	(
		[Id] ASC
	)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]	
)
INSERT INTO {0}
	{1}
", _Filter_TempTableName, FilterSQL.CommandString));
            sqlInits = sqlInits.Union(casesX.Select(q1 => q1.Value).Union(casesY.Select(q2 => q2.Value)).Select(q3 => q3.SqlInitialization)).ToList();
            //
            string sqlMain = "SELECT\n";
            //
            Dictionary<string, List<ChartDataGeneratorAggregate>> aggregatesToAdd = new Dictionary<string, List<ChartDataGeneratorAggregate>>();
            string agg_Key;
            agg_Key = "";
            foreach (ChartDataGeneratorAggregate feAgg in Aggregates)
            {
                if (!aggregatesToAdd.ContainsKey(agg_Key))
                    aggregatesToAdd.Add(agg_Key, new List<ChartDataGeneratorAggregate>());
                aggregatesToAdd[agg_Key].Add(feAgg);
            }
            var qCasesY_Aggregates = (from x1 in casesY
                                      where x1.Value.Aggregates != null && x1.Value.Aggregates.Count > 0
                                      from x2 in x1.Value.Aggregates
                                      select new
                                      {
                                          Case_Key = x1.Key,
                                          Aggregate_Key = x2.Key,
                                          Aggregate_Index = new int[] { 0 },
                                          Aggregate = x2.Value
                                      }).ToList();
            for (int i1 = 0; i1 < qCasesY_Aggregates.Count; i1++)
            {
                qCasesY_Aggregates[i1].Aggregate_Index[0] = i1 + 1;
            }
            foreach (var fe1 in qCasesY_Aggregates)
            {
                agg_Key = string.Format("Y_{0}_", fe1.Aggregate_Index[0]);
                if (!aggregatesToAdd.ContainsKey(agg_Key))
                    aggregatesToAdd.Add(agg_Key, new List<ChartDataGeneratorAggregate>());
                aggregatesToAdd[agg_Key].Add(fe1.Aggregate);
            }
            //
            string sql1;
            // Grouping columns in SELECT section
            listHelper1 = new DataStructure.ListHelper<string>();
            //
            int sqlUID = 1;
            int aggColumnId = 1;
            List<ChartDataGeneratorAggregateColumn> aggregatesColumns = new List<ChartDataGeneratorAggregateColumn>();
            foreach (var fe1 in aggregatesToAdd)
            {
                foreach (ChartDataGeneratorAggregate feAgg in fe1.Value)
                {
                    feAgg.SqlRandomKey = sqlRandomKey;
                    feAgg.SqlUniqueId = sqlUID;
                    feAgg.SqlTempTableName_Filter = string.Format("#Filter_Members_{0}", sqlRandomKey);
                    if (BeforeAggregateUse != null)
                        BeforeAggregateUse(this, feAgg);
                    //
                    if (!string.IsNullOrEmpty(feAgg.SqlInitialization) && !sqlInits.Contains(feAgg.SqlInitialization))
                        sqlInits.Add(feAgg.SqlInitialization);
                    //
                    if (feAgg.Columns != null)
                    {
                        foreach (var feAggColumn in feAgg.Columns)
                        {
                            feAggColumn._Id = aggColumnId;
                            aggColumnId++;
                            feAggColumn._Aggregate = feAgg;
                            feAggColumn._AggregateKey = fe1.Key;
                            aggregatesColumns.Add(feAggColumn);
                            //
                            if (string.IsNullOrEmpty(feAggColumn.Sql))
                                s1 = filterPrimaryColumnName;
                            else
                                s1 = feAggColumn.Sql;
                            listHelper1.Source.Add(string.Format("\n\t(\n\t\t{0}\n\t) AS [g_Agg_{2}{1}_{3}]", s1, feAgg.Id, fe1.Key, feAggColumn._Id));
                        }
                    }
                    //
                    sqlUID++;
                }
            }
            //
            foreach (var fe2 in casesX)
                foreach (var fe1 in fe2.Value.ColumnsSQLs)
                    listHelper1.Source.Add(string.Format("\n\t(\n\t\t{0}\n\t) AS {1}", fe1.Value, fe1.Key));
            if (casesY.Count > 0)
                foreach (var fe2 in casesY)
                    foreach (var fe1 in fe2.Value.ColumnsSQLs)
                        listHelper1.Source.Add(string.Format("\n\t(\n\t\t{0}\n\t) AS {1}", fe1.Value, fe1.Key));
            sqlMain += listHelper1.ToSeperated();

            // FROM section - Main + Join clauses
            sqlMain += "\nFROM \n";
            sqlMain += string.Format("\t#Filter_Members_{0} AS {1}\n", sqlRandomKey, Filter_TableName);
            foreach (string fe1 in (from x3 in aggregatesToAdd
                                    from x1 in x3.Value
                                    from x2 in x1.JoinClauses
                                    select x2).Union(from x1 in aggregatesColumns.Where(q1 => q1.JoinClauses != null)
                                                     from x2 in x1.JoinClauses
                                                     select x2).Distinct())
                sqlMain += string.Format("\t{0}\n", fe1);
            foreach (var fe2 in casesX)
                foreach (string fe1 in fe2.Value.JoinClauses)
                    sqlMain += string.Format("\t{0}\n", fe1);
            if (casesY.Count > 0)
                foreach (var fe2 in casesY)
                    foreach (string fe1 in fe2.Value.JoinClauses)
                        sqlMain += string.Format("\t{0}\n", fe1);
            // CREATE FINAL SQL WITH AGGREGATION
            sql1 = sqlMain;
            sqlMain = "SELECT";
            // Grouping columns
            listHelper1 = new DataStructure.ListHelper<string>();
            foreach (var fe2 in casesX)
                foreach (var fe1 in fe2.Value.ColumnsSQLs)
                    listHelper1.Source.Add(string.Format("\n\t[g_Table].{0}", fe1.Key));
            if (casesY.Count > 0)
                foreach (var fe2 in casesY)
                    foreach (var fe1 in fe2.Value.ColumnsSQLs)
                        listHelper1.Source.Add(string.Format("\n\t[g_Table].{0}", fe1.Key));

            // Aggregate columns in SELECT section
            foreach (var feAggColumn in aggregatesColumns)
            {
                //if (BeforeAggregateUse != null)
                //    BeforeAggregateUse(this, feAgg);
                //
                s1 = "";
                switch ((feAggColumn.Function == null) ? feAggColumn._Aggregate.Function : feAggColumn.Function.Value)
                {
                    case ChartDataGeneratorAggregateFunctions.Sum:
                        s1 = "SUM({0})";
                        break;
                    case ChartDataGeneratorAggregateFunctions.Average:
                        s1 = "AVG({0})";
                        break;
                    case ChartDataGeneratorAggregateFunctions.Max:
                        s1 = "MAX({0})";
                        break;
                    case ChartDataGeneratorAggregateFunctions.Min:
                        s1 = "MIN({0})";
                        break;
                    case ChartDataGeneratorAggregateFunctions.Count:
                    default:
                        s1 = "COUNT({0})";
                        break;
                }
                s1 = string.Format(s1, string.Format("[g_Table].[g_Agg_{1}{0}_{2}]", feAggColumn._Aggregate.Id, feAggColumn._AggregateKey, feAggColumn._Id));
                if (feAggColumn._Aggregate.Credit != null)
                    s1 = string.Format("{0} * {1}", s1, feAggColumn._Aggregate.Credit);
                if (feAggColumn._Aggregate.Constant != null)
                    s1 = string.Format("{0} + {1}", s1, feAggColumn._Aggregate.Constant);
                listHelper1.Source.Add(string.Format("\n\tCONVERT(FLOAT,{0}) AS [g_Value_{2}{1}_{3}]", s1, feAggColumn._Aggregate.Id, feAggColumn._AggregateKey, feAggColumn._Id));
            }
            //
            sqlMain += listHelper1.ToSeperated();

            //FROM
            sqlMain += string.Format("\nFROM\n(\n\t{0}\n) AS [g_Table]\n", sql1);

            // GROUPING section
            sqlMain += "GROUP BY\n";
            listHelper1 = new DataStructure.ListHelper<string>();
            foreach (var fe2 in casesX)
                foreach (string fe1 in fe2.Value.ColumnsSQLs.Keys)
                    listHelper1.Source.Add(fe1);
            if (casesY.Count > 0)
                foreach (var fe2 in casesY)
                    foreach (string fe1 in fe2.Value.ColumnsSQLs.Keys)
                        listHelper1.Source.Add(fe1);
            sqlMain += string.Format("\t{0}", listHelper1.ToSeperated());
            // ORDER section
            sqlMain += string.Format("\nORDER BY\n\t{0}", listHelper1.ToSeperated());

            //
            SqlCommandString sqlCommand1 = new SqlCommandString();
            sqlCommand1.CommandString = string.Format("{0}\n{1}", sqlInits.ToSeperated("\n"), sqlMain);
            sqlCommand1.Params = FilterSQL.Params.ToList();
            foreach (var fe2 in casesX)
                foreach (string fe1 in fe2.Value.Params)
                    if (!sqlCommand1.Params.Contains(fe1))
                        sqlCommand1.Params.Add(fe1);
            if (casesY.Count > 0)
                foreach (var fe2 in casesY)
                    foreach (string fe1 in fe2.Value.Params)
                        if (!sqlCommand1.Params.Contains(fe1))
                            sqlCommand1.Params.Add(fe1);
            //
            ChartDataGeneratorBeforeSqlRunEventArgs args1 = new ChartDataGeneratorBeforeSqlRunEventArgs() { SqlCommandString = sqlCommand1, CommandText = sqlCommand1.GenerateSql() };
            if (BeforeSqlRun != null)
                BeforeSqlRun(this, args1);
            //
            r.AxisX_Text = (from x1 in AxisGeneratorsX
                            select x1.Text).ToSeperated(" | ");
            r.AxisY_Text = Constants.Enum_GetText(Aggregates.First().Function);
            //
            int legendKeyCounter = 0;
            dbCommand.CommandTimeout = 600;
            dbCommand.CommandText = args1.CommandText;
            if (dbCommand.Connection.State != System.Data.ConnectionState.Open)
                dbCommand.Connection.Open();
            System.Data.Common.DbDataReader dbr1 = dbCommand.ExecuteReader();
            while (dbr1.Read())
            {
                string x_Text;
                listHelper1 = new DataStructure.ListHelper<string>();
                for (int iLoop1 = 0; iLoop1 < casesX.Count; iLoop1++)
                {
                    List<object> xText_Values = new List<object>();
                    foreach (var fe1 in casesX[iLoop1].ColumnsSQLs.Keys)
                        xText_Values.Add(dbr1.GetValue(dbr1.GetOrdinal(fe1)));
                    s1 = AxisGeneratorsX[iLoop1].GetPointText(xText_Values);
                    if (!string.IsNullOrEmpty(s1))
                        listHelper1.Source.Add(s1);
                    else
                    {
                        listHelper1.Source.Clear();
                        break;
                    }
                }
                x_Text = listHelper1.ToSeperated(" | ");
                if (string.IsNullOrEmpty(x_Text))
                    continue;
                //
                string y_Text = "";
                var yAggregate = qCasesY_Aggregates.FirstOrDefault();
                yAggregate = null;
                if (casesY.Count > 0)
                {
                    listHelper1 = new DataStructure.ListHelper<string>();
                    foreach (int iLoop1 in casesY.Keys)
                    {
                        //                        if(yAggregates==null && casesY[iLoop1].Aggregates!=null && casesY[iLoop1].
                        List<object> yText_Values = new List<object>();
                        foreach (var fe1 in casesY[iLoop1].ColumnsSQLs.Keys)
                        {
                            object yValue = dbr1.GetValue(dbr1.GetOrdinal(fe1));
                            yText_Values.Add(yValue);
                            //
                            if (yAggregate == null)
                                yAggregate = qCasesY_Aggregates.FirstOrDefault(q => q.Case_Key == iLoop1 && q.Aggregate_Key.Equals(yValue));
                        }
                        s1 = AxisGeneratorsY[iLoop1].GetPointText(yText_Values);
                        if (!string.IsNullOrEmpty(s1))
                            listHelper1.Source.Add(s1);
                    }
                    y_Text = listHelper1.ToSeperated(" | ");
                }
                y_Text = (string.IsNullOrEmpty(y_Text) && (AxisGeneratorsY == null || AxisGeneratorsY.Count == 0)) ? "NULL" : y_Text;
                //
                if (string.IsNullOrEmpty(y_Text))
                    continue;
                //
                ChartDataPoint p1;
                p1 = (from x1 in r.Points
                      where x1.X_Text == x_Text
                      select x1).FirstOrDefault();
                if (p1 == null)
                {
                    p1 = new ChartDataPoint();
                    p1.X_Text = x_Text;
                    r.Points.Add(p1);
                }
                //
                foreach (ChartDataGeneratorAggregateColumn feAggColumn in aggregatesColumns)
                {
                    string legend_Text;
                    if (y_Text == "NULL")
                        legend_Text = feAggColumn.Text;
                    else if (string.IsNullOrEmpty(feAggColumn.Text))
                        legend_Text = y_Text;
                    else
                        legend_Text = string.Format("{0} - {1}", y_Text, feAggColumn.Text);
                    //
                    if (string.IsNullOrEmpty(legend_Text))
                        legend_Text = "-";
                    //
                    int legend_Key;
                    if (!r.Legends.ContainsValue(legend_Text))
                    {
                        legendKeyCounter++;
                        r.Legends.Add(legendKeyCounter, legend_Text);
                        legend_Key = legendKeyCounter;
                    }
                    else
                        legend_Key = r.Legends.First(q => q.Value == legend_Text).Key;
                    //
                    if (!p1.Value.ContainsKey(legend_Key))
                        p1.Value.Add(legend_Key, new Dictionary<int, double?>());
                    //
                    int iOrdinal = dbr1.GetOrdinal(string.Format("g_Value_{1}{0}_{2}", feAggColumn._Aggregate.Id, feAggColumn._AggregateKey, feAggColumn._Id));
                    if (!p1.Value[legend_Key].ContainsKey(feAggColumn._Aggregate.Id))
                        p1.Value[legend_Key].Add(feAggColumn._Aggregate.Id, 0);
                    p1.Value[legend_Key][feAggColumn._Aggregate.Id] += dbr1.IsDBNull(iOrdinal) ? null : (double?)dbr1.GetDouble(iOrdinal);
                }
                //foreach (ChartDataGeneratorAggregate feAgg in Aggregates)
                //{
                //    if (!r.Legends.Contains(y_Text))
                //        r.Legends.Add(y_Text);
                //    //
                //    if (!p1.Value.ContainsKey(y_Text))
                //        p1.Value.Add(y_Text, new Dictionary<int, double?>());
                //    //
                //    int iOrdinal = dbr1.GetOrdinal(string.Format("g_Value_{0}", feAgg.Id));
                //    if (!p1.Value[y_Text].ContainsKey(feAgg.Id))
                //        p1.Value[y_Text].Add(feAgg.Id, dbr1.IsDBNull(iOrdinal) ? null : (double?)dbr1.GetDouble(iOrdinal));
                //}
                //if (yAggregate != null)
                //{
                //    if (!p1.Value_Y.ContainsKey(y_Text))
                //        p1.Value_Y.Add(y_Text, new Dictionary<string, double?>());
                //    //
                //    int iOrdinal = dbr1.GetOrdinal(string.Format("g_Value_Y_{1}_{0}", yAggregate.Aggregate.Id, yAggregate.Aggregate_Index[0]));
                //    if (!p1.Value_Y[y_Text].ContainsKey(yAggregate.Aggregate.ToString()))
                //        p1.Value_Y[y_Text].Add(yAggregate.Aggregate.ToString(), dbr1.IsDBNull(iOrdinal) ? null : (double?)dbr1.GetDouble(iOrdinal));
                //}
            }
            //
            if (r != null && r.Points != null)
            {
                foreach (ChartDataPoint feP in r.Points)
                {
                    foreach (int feLegendKey in from x1 in r.Legends.Keys
                                                where !feP.Value.ContainsKey(x1)
                                                select x1)
                    {
                        feP.Value.Add(feLegendKey, new Dictionary<int, double?>());
                    }
                    //
                    foreach (int feLegendKey in r.Legends.Keys)
                    {
                        foreach (ChartDataGeneratorAggregate feAgg in from x1 in Aggregates
                                                                      where !feP.Value[feLegendKey].ContainsKey(x1.Id)
                                                                      select x1)
                        {
                            feP.Value[feLegendKey].Add(feAgg.Id, null);
                        }
                    }
                }
            }
            //
            return r;
        }
    }
}
